# coding=gbk

from datetime import datetime
from Misc.xlDate import read_xl_date
import win32com.client 
from DataAccess.DBConnFactory import DBConnFactory
import time

HTQFII_PORTF_ID = 'SC030'

def HTQFII_preconfirm_import(file_path):
	pkedb_conn = DBConnFactory().get_db_connection('PKEDB')
	
	xApp = win32com.client.Dispatch("Excel.Application")
	xBook = xApp.Workbooks.Open(file_path)
	xSht = xBook.Worksheets(1)
	row = xSht.Range("C65536").End(win32com.client.constants.xlUp).Row + 1

	sql_text = '''merge into transaction trd
	            using (select TO_DATE(:1,'yyyy-mm-dd') as trade_date,
							  :2 as ticker,
							  :3 as trade_type,
							  :4 as portfolio_id,
							  'STOCK' as security_type,
							  :5 as amount,
							  :6 as cost_price,
							  :7 as currency,
							  :8 as market,
							  :9 as settle_date
					    from dual) t
				on (trd.trade_date=t.trade_date and trd.ticker=t.ticker and trd.portfolio_id=t.portfolio_id and trd.security_type=t.security_type)
				when matched then update set
					trd.trade_type=t.trade_type,
					trd.amount=t.amount,
					trd.cost_price=t.cost_price,
					trd.currency=t.currency,
					trd.market=t.market,
					trd.settle_date=t.settle_date
				when not matched then insert (trd.trade_date,trd.ticker,trd.trade_type,trd.portfolio_id,trd.security_type,trd.amount,trd.cost_price,trd.currency,trd.market,trd.settle_date)
					values(t.trade_date,t.ticker,t.trade_type,t.portfolio_id,t.security_type,t.amount,t.cost_price,t.currency,t.market,t.settle_date)'''
	
	for i in range(4, row):	
		
		#trade_date = datetime.strptime(xSht.Cells(i, 18).Value,'%m/%d/%y').date()
		trade_date = read_xl_date(float(xSht.Cells(i, 18).Value))
		
		sys_ticker = str(int(xSht.Cells(i, 3).Value))
		if len(sys_ticker) < 6:
			sys_ticker = '0' * (6 - len(sys_ticker)) + sys_ticker
		
		trade_type = 'BUY' if xSht.Cells(i, 10).Value == 'B' else 'SELL'
		amount = xSht.Cells(i, 11).Value
		cost_price = xSht.Cells(i, 12).Value
		currency = xSht.Cells(i, 7).Value
		#market = xSht.Cells(i, 4).Value
		market = ''
		#settle_date = datetime.strptime(xSht.Cells(i, 9).Value,'%m/%d/%y').date()
		settle_date = read_xl_date(float(xSht.Cells(i, 9).Value))
		
		pkedb_conn.cursor().execute(sql_text, 
						(trade_date.strftime('%Y-%m-%d'),sys_ticker,trade_type,HTQFII_PORTF_ID,amount,cost_price,currency,market,settle_date))
		pkedb_conn.commit()
		
	xBook.Close(SaveChanges = 0)
	del xApp
		
	print '	   info, Step 1.5: HTQFII pre-confirm import completed.'
		
def HTQFII_confirm_import(file_path):
	pkedb_conn = DBConnFactory().get_db_connection('PKEDB')
	
	xApp = win32com.client.Dispatch("Excel.Application")
	xBook = xApp.Workbooks.Open(file_path)
	xSht = xBook.Worksheets("HTAM QFII Sub AC 2")
	row = xSht.Range("D65536").End(win32com.client.constants.xlUp).Row + 1

	sql_text = '''merge into transaction trd
	            using (select TO_DATE(:1,'yyyy-mm-dd') as trade_date,
							  :2 as ticker,
							  :3 as trade_type,
							  :4 as portfolio_id,
							  'STOCK' as security_type,
							  :5 as amount,
							  :6 as cost_price,
							  :7 as currency,
							  :8 as market,
							  :9 as settle_date
					    from dual) t
				on (trd.trade_date=t.trade_date and trd.ticker=t.ticker and trd.portfolio_id=t.portfolio_id and trd.security_type=t.security_type)
				when matched then update set
					trd.trade_type=t.trade_type,
					trd.amount=t.amount,
					trd.cost_price=t.cost_price,
					trd.currency=t.currency,
					trd.market=t.market,
					trd.settle_date=t.settle_date
				when not matched then insert (trd.trade_date,trd.ticker,trd.trade_type,trd.portfolio_id,trd.security_type,trd.amount,trd.cost_price,trd.currency,trd.market,trd.settle_date)
					values(t.trade_date,t.ticker,t.trade_type,t.portfolio_id,t.security_type,t.amount,t.cost_price,t.currency,t.market,t.settle_date)'''

	trade_date = read_xl_date(float(xSht.Cells(6, 3).Value))
	settle_date = read_xl_date(float(xSht.Cells(7, 3).Value))
	
	for i in range(13, row):	
		
		sys_ticker = xSht.Cells(i, 4).Value
		trade_type = xSht.Cells(i, 6).Value
		amount = xSht.Cells(i, 7).Value
		cost_price = abs(xSht.Cells(i, 18).Value/amount)
		currency = 'CNY'
		market = ''
		
		pkedb_conn.cursor().execute(sql_text, 
						(trade_date.strftime('%Y-%m-%d'),sys_ticker,trade_type,HTQFII_PORTF_ID,amount,cost_price,currency,market,settle_date))
		pkedb_conn.commit()	
	
	xBook.Close(SaveChanges = 0)
	del xApp
		
	print '	   info, Step 1.5: HTQFII confirm import completed.'